package com.aegean.MonitorExamSYS.examinee.dao.impl;

import java.sql.Blob;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.annotation.Resource;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Restrictions;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.aegean.MonitorExamSYS.center.component.ConductionInfoForExaminee;
import com.aegean.MonitorExamSYS.examinee.component.ExamineeExam;
import com.aegean.MonitorExamSYS.examinee.dao.ExamineeDao;
import com.aegean.MonitorExamSYS.tools.MyTools;
import com.aegean.MonitorExaxSYS.model.BigType;
import com.aegean.MonitorExaxSYS.model.ConductionValue;
import com.aegean.MonitorExaxSYS.model.ExamInfo;
import com.aegean.MonitorExaxSYS.model.ExamPositionQualification;
import com.aegean.MonitorExaxSYS.model.ExamineeQualification;
import com.aegean.MonitorExaxSYS.model.ExamineeValue;
import com.aegean.MonitorExaxSYS.model.MonitorQualification;
import com.aegean.MonitorExaxSYS.model.MonitorValue;
import com.aegean.MonitorExaxSYS.model.SmallType;
import com.aegean.MonitorExaxSYS.model.component.Photo;
import com.aegean.MonitorExaxSYS.model.component.Place;

@Repository
@Transactional
public class ExamineeDaoImpl implements ExamineeDao {
	@Resource
	SessionFactory factory;

	@Override
	public ExamineeValue insertUser(ExamineeValue one) {
		// TODO Auto-generated method stub
		Session session;
		ExamineeValue two;
		try {
			two = one;
			session = factory.getCurrentSession();
			if (one != null && one instanceof ExamineeValue) {
				session.save(two);
				return two;
			} else {
				return null;
			}
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}

	}

	@Override
	public ExamineeValue insertUserLocation(ExamineeValue one, float Lat,
			float Lng, String address, String route, String city, String region) {
		// TODO Auto-generated method stub
		Session session;
		ExamineeValue user;
		try {
			session = factory.getCurrentSession();
			if (one != null && one instanceof ExamineeValue) {
				user = (ExamineeValue) session.get(ExamineeValue.class,
						one.getUser_Id());
				Place place = new Place();
				place.setPlace_H(Lat);
				place.setPlace_V(Lng);
				place.setPlace_Address(address);
				place.setPlace_Region(route);
				place.setPlace_City(city);
				place.setPlace_Region(region);
				user.setExamineeValue_Address(place);
				return user;
			} else {
				return null;
			}
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public ExamineeValue loginUser(String user_Login, String user_Pwd) {
		// TODO Auto-generated method stub
		Session session;
		ExamineeValue user;
		try {
			session = factory.getCurrentSession();
			Criteria cr = session.createCriteria(ExamineeValue.class);
			cr.add(Restrictions.eq("user_Login", user_Login));
			cr.add(Restrictions.eq("user_Password", user_Pwd));
			cr.setCacheable(true);
			user = (ExamineeValue) cr.uniqueResult();
			if (user == null) {
				return null;
			} else {
				return user;
			}
		} catch (Exception e) {
			e.printStackTrace();
			return null;
			// TODO: handle exception
		}
	}

	@Override
	public ExamineeValue updateUser(int Customer_Id, ExamineeValue one,
			Photo photo) {
		// TODO Auto-generated method stub
		Session session;
		ExamineeValue user;
		try {
			session = factory.getCurrentSession();
			user = (ExamineeValue) session.load(ExamineeValue.class,
					Customer_Id);
			user.setUser_RealName(one.getUser_RealName());
			user.setUser_Sex(one.getUser_Sex());
			user.setExamineeValue_Age(one.getExamineeValue_Age());
			user.setExamineeValue_Email(one.getExamineeValue_Email());
			user.setExamineeValue_Phone(one.getExamineeValue_Phone());
			user.setExamineeValue_IdentityCard(one
					.getExamineeValue_IdentityCard());
			if (photo == null) {
			} else {
				user.setUser_Photo(photo);
			}
			return user;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public Blob showPhoto(int Customer_Id) {
		// TODO Auto-generated method stub
		Session session;
		ExamineeValue user;
		try {
			session = factory.getCurrentSession();
			user = (ExamineeValue) session
					.get(ExamineeValue.class, Customer_Id);
			return user.getUser_Photo().getPhoto();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public ExamineeValue updateUserPwd(int Customer_Id, String newPwd) {
		// TODO Auto-generated method stub
		Session session;
		ExamineeValue user;
		try {
			session = factory.getCurrentSession();
			user = (ExamineeValue) session
					.get(ExamineeValue.class, Customer_Id);
			user.setUser_Password(newPwd);
			return user;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public ExamineeValue updateUserLocation(ExamineeValue one, float Lat,
			float Lng, String address, String route, String city, String region) {
		Session session;
		ExamineeValue user;
		try {
			session = factory.getCurrentSession();
			if (one != null && one instanceof ExamineeValue) {
				user = (ExamineeValue) session.get(ExamineeValue.class,
						one.getUser_Id());
				Place place = new Place();
				place.setPlace_H(Lat);
				place.setPlace_V(Lng);
				place.setPlace_Address(address);
				place.setPlace_Route(route);
				place.setPlace_City(city);
				place.setPlace_Region(region);
				user.setExamineeValue_Address(place);
				return user;
			} else {
				return null;
			}
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public int searchExamValueCount(int small_Id) {
		Session session = null;
		try {
			session = factory.getCurrentSession();
			Query q = session
					.createSQLQuery(
							"select * from examinfo where EXAMINFO_TYPE=? and EXAMINFO_STATE=0")
					.addEntity(ExamInfo.class);
			q.setInteger(0, small_Id);
			q.setCacheable(true);
			return q.list().size();
		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		}
	}

	@Override
	public List<ExamInfo> searchPartExamValues(int small_Id, int start,
			int limit) {
		Session session = null;
		try {
			session = factory.getCurrentSession();
			Query q = session
					.createSQLQuery(
							"select * from examinfo where EXAMINFO_TYPE=? and EXAMINFO_STATE=0 order by EXAMINFO_CREATETIME desc")
					.addEntity(ExamInfo.class);
			q.setInteger(0, small_Id);
			q.setFirstResult(start);
			q.setMaxResults(limit);
			q.setCacheable(true);
			return (List<ExamInfo>) q.list();
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public List<ConductionValue> showConducation(int exam_Id) {
		// TODO Auto-generated method stub
		Session session;
		try {
			session = factory.getCurrentSession();
			Query q = session
					.createSQLQuery(
							"select * from conductionvalue as c where c.conduction_id in (select EXAMPQ_CONDUCTION from exampositionqualification as e where e.exampq_exam="
									+ exam_Id + ")").addEntity(
							ConductionValue.class);
			q.setCacheable(true);
			return q.list();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public boolean applyQualification(int conduction_Id, int examinee_Id,
			int examInfo_Id) {
		ExamineeValue examinee;
		ExamPositionQualification epq;
		ExamineeQualification eq;
		ExamInfo examinfo;
		Session session;
		try {
			session = factory.getCurrentSession();
			examinee = (ExamineeValue) session.load(ExamineeValue.class,
					examinee_Id);
			examinfo = (ExamInfo) session.load(ExamInfo.class, examInfo_Id);
			Criteria cr = session
					.createCriteria(ExamPositionQualification.class);
			cr.add(Restrictions.eq("examPQ_Exam.examInfo_Id", examInfo_Id));
			cr.add(Restrictions.eq("examPQ_Conduction.conduction_Id",
					conduction_Id));
			cr.setCacheable(true);
			epq = (ExamPositionQualification) cr.uniqueResult();
			eq = new ExamineeQualification();
			eq.setExaminee_PQ(epq);
			eq.setExaminee_User(examinee);
			eq.setExaminee_State(0);
			session.save(eq);
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return false;
		}

		// TODO Auto-generated method stub
	}

	@Override
	public int validationExam(int exam_Id, Date startTime, Date endTime,
			int user_Id) {
		// TODO Auto-generated method stub
		Session session;
		try {
			session = factory.getCurrentSession();
			String hql = "from ExamineeQualification as eq,ExamineeValue as e,ExamPositionQualification as epq,ExamInfo as exam"
					+ " where e.user_Id=:user_Id and eq.examinee_User=e and eq.examinee_PQ=epq and epq.examPQ_Exam=exam and (exam.examInfo_Id=:exam_Id or examInfo_StartTime between :startTime and :endTime or examInfo_EndTime between :startTime and :endTime) ";
			Query q = session.createQuery(hql);
			q.setInteger("exam_Id", exam_Id);
			q.setInteger("user_Id", user_Id);
			q.setDate("startTime", startTime);
			q.setDate("endTime", endTime);
			q.setCacheable(true);
			return q.list().size();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return 0;
		}
	}

	@Override
	public int showExamineeQualifications(int user_Id, String state) {
		// TODO Auto-generated method stub
		Session session;
		try {
			session = factory.getCurrentSession();
			String hql = "select exam.examInfo_Id,exam.examInfo_Name,exam.examInfo_StartTime,exam.examInfo_EndTime,eq.examinee_Id,eq.examinee_Identity,eq.examinee_State,con.conduction_Id,con.conduction_Name,ep.examPosition_Id,ep.examPosition_Name,ep.examPosition_Place.place_H,ep.examPosition_Place.place_V from ExamineeQualification as eq,ExamineeValue as e,ExamPosition as ep,PositionQualification as pq,ExamPositionQualification as epq,ExamInfo as exam,ConductionValue as con"
					+ " where eq.examinee_User=e and e.user_Id=:user_Id and eq.examinee_PQ=epq and ((eq.examineePQ=pq and pq.pq_Ep=ep)or(eq.examineePQ=null)) and epq.examPQ_Exam=exam and exam.examInfo_State in "
					+ state
					+ " and epq.examPQ_Conduction=con"
					+ " group by exam.examInfo_Id order by exam.examInfo_StartTime desc";
			Query q = session.createQuery(hql);
			q.setInteger("user_Id", user_Id);
			q.setCacheable(true);
			return q.list().size();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return 0;
		}
	}

	@Override
	public List<ExamineeExam> showExamineeQualificationsList(int user_Id,
			int start, int limit, String state) {
		// TODO Auto-generated method stub
		Session session;
		List<ExamineeExam> exs = new ArrayList<ExamineeExam>();
		try {
			session = factory.getCurrentSession();
			String hql = "select exam.examInfo_Id,exam.examInfo_Name,exam.examInfo_StartTime,exam.examInfo_EndTime,eq.examinee_Id,eq.examinee_Identity,eq.examinee_State,con.conduction_Id,con.conduction_Name,ep.examPosition_Id,ep.examPosition_Name,ep.examPosition_Place.place_H,ep.examPosition_Place.place_V from ExamineeQualification as eq,ExamineeValue as e,ExamPosition as ep,PositionQualification as pq,ExamPositionQualification as epq,ExamInfo as exam,ConductionValue as con"
					+ " where eq.examinee_User=e and e.user_Id=:user_Id and eq.examinee_PQ=epq and ((eq.examineePQ=pq and pq.pq_Ep=ep)or(eq.examineePQ=null)) and epq.examPQ_Exam=exam and exam.examInfo_State in "
					+ state
					+ " and epq.examPQ_Conduction=con"
					+ " group by exam.examInfo_Id order by exam.examInfo_StartTime desc";
			Query q = session.createQuery(hql);
			q.setInteger("user_Id", user_Id);
			q.setCacheable(true);
			q.setFirstResult(start);
			q.setMaxResults(limit);
			List l = q.list();
			for (int i = 0; i < l.size(); i++) {
				Object[] obj = (Object[]) l.get(i);
				ExamineeExam one = new ExamineeExam();
				one.setExam_Id(Integer.parseInt(obj[0].toString().trim()));
				one.setExma_Name(obj[1].toString().trim());
				one.setExam_startTime(obj[2].toString().trim());
				one.setExam_endTime(obj[3].toString().trim());
				one.setExamineeQ_Id(Integer.parseInt(obj[4].toString().trim()));
				if (obj[5] == null) {
					one.setExamineeQ_Identity("无");
				} else {
					one.setExamineeQ_Identity(obj[5].toString().trim());
				}
				one.setExamineeQ_State(Integer.parseInt(obj[6].toString()
						.trim()));
				one.setPlace_Id(Integer.parseInt(obj[7].toString().trim()));
				one.setPlace_Name(obj[8].toString().trim());
				one.setPosition_Id(Integer.parseInt(obj[9].toString().trim()));
				one.setPosition_Name(obj[10].toString().trim());
				one.setP_H(Float.parseFloat((obj[11].toString().trim())));
				one.setP_V(Float.parseFloat((obj[12].toString().trim())));
				exs.add(one);
			}
			return exs;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public boolean validateUser(String user_Login) {
		// TODO Auto-generated method stub
		Session session;
		ExamineeValue user;
		try {
			session = factory.getCurrentSession();
			Criteria cr = session.createCriteria(ExamineeValue.class);
			cr.add(Restrictions.eq("user_Login", user_Login));
			cr.setCacheable(true);
			user = (ExamineeValue) cr.uniqueResult();
			if (user == null) {
				return true;
			} else {
				return false;
			}
		} catch (Exception e) {
			e.printStackTrace();
			return false;
			// TODO: handle exception
		}
	}

	@Override
	public int countOfcurrentAppliedPersonCount(int exam_Id, int conduction_Id) {
		// TODO Auto-generated method stub
		Session session;
		try {
			session = factory.getCurrentSession();
			String sql = "SELECT epq.exampq_exam,COUNT(eq.examinee_id) FROM examineequalification AS eq,exampositionqualification AS epq WHERE eq.examinee_pq=epq.exampq_id AND epq.exampq_conduction="
					+ conduction_Id
					+ " and epq.exampq_exam="
					+ exam_Id
					+ " GROUP BY epq.exampq_exam";
			Query q = session.createSQLQuery(sql);
			List l = q.list();
			if (l.size() == 0) {
				return 0;
			} else {
				Object[] obj = (Object[]) l.get(0);
				return Integer.valueOf(obj[1].toString());
			}
		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		}
	}

	@Override
	public int countOfcurrentExamAvailablePersonCount(int conduction_Id,
			String start_Time, String end_Time) {
		Session session;
		List<MonitorQualification> mqs;
		try {
			session = factory.getCurrentSession();
			String hql = "from MonitorQualification where (monitor_Position.pq_Epq.examPQ_Exam.examInfo_StartTime between :startTime and :endTime or monitor_Position.pq_Epq.examPQ_Exam.examInfo_EndTime between :startTime and :endTime) and monitor_User.monitor_Unit.conduction_Id=:unit_Id ";
			Query q = session.createQuery(hql);
			q.setString("startTime", start_Time);
			q.setString("endTime",end_Time);
			q.setInteger("unit_Id", conduction_Id);
			q.setCacheable(true);
			mqs = q.list();
			String ids = "";
			for (MonitorQualification one : mqs) {
				ids += one.getMonitorQ_Id() + ",";
			}
			if (ids.endsWith(",")) {
				int len = ids.length();
				ids = ids.substring(0, len - 1).trim();
			}
			String sql2 = "SELECT us.*,m.* FROM monitorvalue AS m,uservalue AS us,bigtype AS big WHERE us.USER_ID=m.USER_ID AND m.MONITOR_BIG=big.BIG_ID AND m.MONITOR_STATE=1 AND m.MONITOR_UNIT="
					+ conduction_Id;
			if (mqs.size() == 0) {

			} else {
				sql2 += " AND m.USER_ID NOT IN (SELECT mq.MONITOR_USER FROM monitorqualification AS mq WHERE mq.MONITORQ_ID IN ("
						+ ids + ")) order by m.monitor_big";
			}
			Query q2 = session.createSQLQuery(sql2).addEntity("monitor",
					MonitorValue.class);
			q2.setCacheable(true);
			int monitorCount=q2.list().size();
			String sql = "select ep.examposition_id,ep.examposition_personcount from examposition as ep,conductionvalue as con where ep.examposition_unit=con.conduction_id and con.conduction_id ="
					+ conduction_Id
					+ " and ep.examposition_id not in (select pq.pq_ep from positionqualification as pq,exampositionqualification as eq,examinfo as exam where pq.pq_epq=eq.exampq_id and eq.exampq_exam=exam.examinfo_id and (exam.examinfo_starttime  between date_format(:startTime,'%Y-%m-%d %H:%i:%s') and date_format(:endTime,'%Y-%m-%d %H:%i:%s') || exam.examinfo_endtime  between date_format(:startTime,'%Y-%m-%d %H:%i:%s') and date_format(:endTime,'%Y-%m-%d %H:%i:%s'))) ORDER BY ep.examposition_personcount DESC limit 0,"+monitorCount/2;
			Query q3 = session.createSQLQuery(sql);
			q3.setString("startTime", start_Time);
			q3.setString("endTime", end_Time);
			List l = q3.list();
			if (l.size() == 0) {
				return 0;
			} else {
				int includeCount=0;
				for (int i = 0; i < l.size(); i++) {
					Object[] obj = (Object[]) l.get(i);
					includeCount+=Integer.valueOf(obj[1].toString());
				}
				return includeCount;
			}
		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		}
	}
}
